A query optimizer is a component of a database management system that attempts to determine the most efficient way to execute a query statement. The optimizer considers the possible query execution plans for a given query statement and attempts to determine which of those plans will be the most efficient or cost effective. However, query statements with host variables and parameter markers in predicates may limit an optimizer's visibility regarding the data characteristics and predicate selectivity. As a consequence, the optimizer may take a guess during the course of cost estimation on the access path chosen for retrieving data during query execution. The access path selected by the optimizer is, therefore, unpredictable from a performance standpoint. Due to this limited visibility, the optimizer may select a sub-optimal access path which exposes the performance issues immediately, may select a reasonably good but not the optimal access path, or may select the optimal access path by chance since multiple occurrences of variables may cancel each other or issues with other optimizer input could offset the impact of the variables.
One existing approach is to modify an application source code to use literals in the predicates instead of host variables and parameter markers. With this approach, the optimizer is able to use all the available optimizer statistics to properly estimate the predicate selectivity. However, this approach increases the prepare cost since very likely the statements will no longer be repeating queries. The performance benefit does not necessarily justify the increased prepare costs.
Another existing approach uses re-optimization bind options to re-optimize the statement when literals are known at execution time. These options defer the optimization to the first execution, every execution, or selective executions when literals are known to the optimizer. However, when deferred to the first execution, the literals used may be outliers and produce an access path that is inefficient for the overall workload. The overhead costs of deferring the optimization to every execution or selective executions may not justify the performance benefits. Moreover, these options are usually supported by data servers at the package level and thus would apply to the whole package, which forces other statements in the package to incur the re-optimization costs unnecessarily.
Another existing approach uses optimizer hints to lock down the access path for statements that are subject to instability. However, this requires users to identify the affected statements and determine the best access path for each of them. This requires significant user bandwidth and the skill to implement.
Another existing approach is to support “parametric” access paths in the database server. Here, the optimizer would generate multiple access paths and associate the proper “predicate profile: for each of the access paths thus generated. At execution time, an access path is dynamically selected based on the predicate profile and the statement execution is dispatched accordingly. However, this solution is costly to implement. Further, when database applications are running against multiple data servers, not all data servers may support this feature.